﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for AccountDAO
/// </summary>
public class AccountDAO:DBAccess,IAccount
{
    #region sql command
    public int roleID;
    string SQL_SELECT_ALL = "select * from Accounts";
    string SQL_LOGIN = "Select * from Accounts where Username = @userID and Password = @password";
    string SQL_SELECT_BY_ROLEID = "select * from Accounts where RoleID=@id";
    string SQL_INSERT = "insert into Accounts(Username, Password, RoleID) values (@username, @pass, @id)";
    string SQL_UPDATE = "update Accounts set Username=@username, Password=@password where RoleID=@id";
    string SQL_DELETE = "delete from Accounts where RoleID=@id";
    private string SQL_GET_PASSWORD = "select Password from Accounts where Username = @username";
    private string SQL_CHANGE_PASSWORD = "update Accounts set Password = @password where Username = @username";
    private string SQL_BLOCK_ACCOUNT = "update Accounts set RoleID = '6' where UserName = @userName";
    private string SQL_UNBLOCK_ACCOUNT = "update Accounts set RoleID = @roleID where UserName = @userName";
    #endregion

    #region fields
    string message;
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    public AccountDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    
    #region methods
    /// <summary>
    /// Login
    /// </summary>
    /// <returns>bool</returns>
    public bool Login(string username, string password)
    {
        AccountInfo acc = new AccountInfo();
        try
        {
            SqlCommand command = new SqlCommand(SQL_LOGIN, conn);
            command.Parameters.AddWithValue("@userID",username);
            command.Parameters.AddWithValue("@password", password);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {   
                    roleID = dr.GetInt32(2);
                    return true;
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }         
        return false;
    }
    /// <summary>
    /// Get all Account
    /// </summary>
    /// <returns>IList</returns>
    public IList<AccountInfo> GetAll()
    {
        IList<AccountInfo> list = new List<AccountInfo>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    AccountInfo acc = new AccountInfo();
                    acc.RoleID = dr.GetInt32(0);
                    acc.Username = dr.GetString(1);
                    acc.Password = dr.GetString(2);
                    list.Add(acc);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    /// <summary>
    /// Method GetByID
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    AccountInfo IAccount.GetByID(string id)
    {
        AccountInfo acc = new AccountInfo();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ROLEID, conn);
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    acc.RoleID = dr.GetInt32(0);
                    acc.Username=dr.GetString(1);
                    acc.Password = dr.GetString(2);  
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return acc;
    }


    /// <summary>
    /// Insert Account
    /// </summary>
    /// <returns>int</returns>
    public int Insert(AccountInfo account)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@id", account.RoleID);
            command.Parameters.AddWithValue("@username", account.Username);
            command.Parameters.AddWithValue("@pass", account.Password);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
               // command.CommandText = SQL_SELECT_BY_ROLEID;
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Update Account return number of record updated
    /// </summary>
    /// <returns>int</returns>
    public int Update(AccountInfo account)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@username", account.Username);
            command.Parameters.AddWithValue("@pass", account.Password);
            command.Parameters.AddWithValue("@id",account.RoleID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    /// <summary>
    /// Delete Account
    /// </summary>
    /// <returns>int</returns>
    public int Delete(AccountInfo account)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id",account.RoleID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }


    /// <summary>
    /// Get password
    /// </summary>
    /// <returns>string</returns>
    public string GetPassword(string username)
    {
        AccountInfo acc = new AccountInfo();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_GET_PASSWORD, conn);
            cmd.Parameters.AddWithValue("@username", username);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    acc.Password = reader.GetString(0);
                    break;
                }
            }
            Disconnect();
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return acc.Password;
    }

    /// <summary>
    /// Change Password
    /// </summary>
    /// <returns>int</returns>
    public int ChangePassword(string username,string pass)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_CHANGE_PASSWORD, conn);
            cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@password", pass);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();                
            }
            Disconnect();
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }
    #endregion


    /// <summary>
    /// Method to block Account
    /// </summary>
    /// <returns>int</returns>
    public int BlockAccount(string username)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_BLOCK_ACCOUNT, conn);
            command.Parameters.AddWithValue("@username", username);
           
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    /// <summary>
    /// Method to Unblock Account
    /// </summary>
    /// <returns>int</returns>
    public int UnBlockAccount(string username,int roleID)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UNBLOCK_ACCOUNT, conn);
            command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@roleID", roleID);

            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
}